/*
 * StoreProductDAO.java
 *
 * Created on February 12, 2007, 9:10 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E.data;
import edu.byu.isys413.group1E.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author tkulbeth
 */
public class StoreProductDAO {
    
    
    private static StoreProductDAO instance = null;
    
    /** Creates a new instance of StoreProductDAO */
    public StoreProductDAO() {
    }
    
    
    /**Retrieves an instance of the StoreProductDAO object**/
    public static synchronized StoreProductDAO getInstance() {
        if (instance == null) {
            instance = new StoreProductDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     * 
     * @param storeID 
     * @param conceptualID 
     * @throws java.lang.Exception 
     * @return 
     */
    public StoreProductBO create(String storeID, String conceptualID) throws Exception{
        StoreProductBO bo = new StoreProductBO(storeID, conceptualID);
        Cache c = Cache.getInstance();
        c.put(bo.getStoreID() + bo.getConceptualID(), bo);
        return bo;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     */
    public synchronized StoreProductBO read(String storeID, String conceptualID) throws DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        StoreProductBO StoreProduct = (StoreProductBO)c.get(storeID+conceptualID);
        if (StoreProduct != null){
            return StoreProduct;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            StoreProduct = this.read(storeID, conceptualID, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id=" + storeID + " + " + conceptualID, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return StoreProduct;
    }
    
    
    synchronized StoreProductBO read(String storeID, String conceptualID, Connection conn) throws SQLException, DataException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        StoreProductBO StoreProduct = (StoreProductBO)c.get(storeID+conceptualID);
        if (StoreProduct != null){
            return StoreProduct;                                                        // if so, return it immediately
        }
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Store_Product WHERE StoreID = '" + storeID + "' AND ProductID = '"+conceptualID+"'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            StoreProduct = new StoreProductBO(rs.getString("storeid"),rs.getString("productid"));
            StoreProduct.setShelfLocation(rs.getString("shelflocation"));
            StoreProduct.setQtyOnHand(rs.getInt("qtyonhand"));
            StoreProduct.setReorderPoint(rs.getInt("reorderpoint"));
            StoreProduct.setQtyToOrder(rs.getInt("qtytoorder"));
            StoreProduct.setQtyOnOrder(rs.getInt("qtyonorder"));
            
            StoreProduct.setAlreadyInDB(true);
            StoreProduct.setDirty(false);
            // save to the cache
            c.put(storeID+conceptualID, StoreProduct);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return StoreProduct;
    }
    
    /**
     *Reads in an ArrayList of Store_Product objects based on a conceptual key
     *
     */
    public synchronized ArrayList<StoreProductBO> readByConceptualID(String conceptualID) throws SQLException, DataException, ConnectionPoolException  {
        ArrayList<StoreProductBO> StoreProductList = new ArrayList<StoreProductBO>();
        
        //get connection
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Store_Product WHERE ConceptualID = '"+conceptualID+"'");
        conn.commit();
        
        //build the list with the table data
        while(rs.next()){
            StoreProductBO StoreProduct = read(rs.getString("StoreID"),rs.getString("ConceptualID"));  
            StoreProduct.setAlreadyInDB(true);
            StoreProductList.add(StoreProduct);
        }
        
        stmt.close();
        //release the connection
        ConnectionPool.getInstance().release(conn);
        //return the list of AOI
        return StoreProductList;        
    }//end readByConceptualID
    
    /**
     * 
     * @param storeID 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.group1E.DataException 
     * @throws edu.byu.isys413.group1E.ConnectionPoolException 
     * @return 
     */
    public synchronized ArrayList<StoreProductBO> readByStoreID(String storeID) throws SQLException, DataException, ConnectionPoolException  {
        ArrayList<StoreProductBO> StoreProductList = new ArrayList<StoreProductBO>();
        
        //get connection
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Store_Product WHERE storeID = '"+storeID+"'");
        conn.commit();
        
        //build the list with the table data
         while(rs.next()){
            StoreProductBO StoreProduct = read(rs.getString("StoreID"),rs.getString("ConceptualID"));
            StoreProduct.setAlreadyInDB(true);
            StoreProductList.add(StoreProduct);
        }
        
        stmt.close();
        //release the connection
        ConnectionPool.getInstance().release(conn);
        //return the list of AOI
        return StoreProductList;        
    }//end readByConceptualID
    
    
    /*Read entire contents of the StoreProduct table*/
    /**
     * 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.group1E.DataException 
     * @throws edu.byu.isys413.group1E.ConnectionPoolException 
     * @return 
     */
    public synchronized ArrayList<StoreProductBO> readAll() throws SQLException, DataException, ConnectionPoolException {
        ArrayList<StoreProductBO> StoreProductList = new ArrayList<StoreProductBO>();
        
        //get connection
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Store_Product");
        conn.commit();
        
        //build the list with the table data
        while(rs.next()){
            StoreProductBO StoreProduct = read(rs.getString("StoreID"),rs.getString("ConceptualID")); 
            StoreProduct.setAlreadyInDB(true);
            StoreProductList.add(StoreProduct);
        }
        
        stmt.close();
        //release the connection
        ConnectionPool.getInstance().release(conn);
        //return the list of AOI
        return StoreProductList;
    }//end readList
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     */
    public synchronized void save(StoreProductBO StoreProduct) throws DataException, ConnectionPoolException {
        StoreProduct.setAlreadyInDB(true);
        try {
            Connection conn = ConnectionPool.getInstance().get();
            try {
                this.save(StoreProduct, conn);                                                // call save with a connection
                conn.commit();
            } catch (DataException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (SQLException ex) {
                conn.rollback();
                ex.printStackTrace();
                throw new SQLException();
            }
            ConnectionPool.getInstance().release(conn);                             // release the connection
        }catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + StoreProduct.getStoreID() + " + " + StoreProduct.getConceptualID(), e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        return;
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the customerDAO calling
     *  AOI_shipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     */
    synchronized void save(StoreProductBO StoreProduct, Connection conn) throws SQLException, DataException {
        // run update or insert
        if (StoreProduct.getDirty()) {
            if (StoreProduct.isAlreadyInDB()) {
                update(StoreProduct, conn);
            }else{
                insert(StoreProduct, conn);
            }
            // set the dirty flag to false now that we've saved it
            StoreProduct.setDirty(false);
        }
        
        // call save(bo, conn) on any subobjects (like CustomerDAO to AOI_shipDAO)
        // touch the cache for the object
        Cache c = Cache.getInstance();
        c.put(StoreProduct.getStoreID()+StoreProduct.getConceptualID(), StoreProduct);
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void update(StoreProductBO StoreProduct, Connection conn) throws SQLException, DataException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Store_Product SET storeid = ?, Productid = ?, shelflocation = ?," +
                "qtyonhand = ?, reorderpoint =?, qtytoorder = ?, qtyonorder = ? WHERE storeid = ? AND productid = ?");
        
        pstmt.setString(1, StoreProduct.getStoreID());
        pstmt.setString(2, StoreProduct.getConceptualID());
        pstmt.setString(3, StoreProduct.getShelfLocation());
        pstmt.setInt(4, StoreProduct.getQtyOnHand());
        pstmt.setInt(5, StoreProduct.getReorderPoint());
        pstmt.setInt(6, StoreProduct.getQtyToOrder());
        pstmt.setInt(7, StoreProduct.getQtyOnOrder());   
        pstmt.setString(8, StoreProduct.getStoreID());
        pstmt.setString(9, StoreProduct.getConceptualID());
        
        pstmt.executeUpdate();
        pstmt.close();
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void insert(StoreProductBO StoreProduct, Connection conn) throws SQLException, DataException {
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Store_Product (storeID, Productid, shelflocation," +
                "qtyonhand, reorderpoint, qtytoorder, qtyonorder) VALUES (?, ?, ?, ?, ?, ?, ?)");
            pstmt.setString(1, StoreProduct.getStoreID());
            pstmt.setString(2, StoreProduct.getConceptualID());
            pstmt.setString(3, StoreProduct.getShelfLocation());
            pstmt.setInt(4, StoreProduct.getQtyOnHand());
            pstmt.setInt(5, StoreProduct.getReorderPoint());
            pstmt.setInt(6, StoreProduct.getQtyToOrder());
            pstmt.setInt(7, StoreProduct.getQtyToOrder());
            
            pstmt.executeUpdate();
            
            StoreProduct.setAlreadyInDB(true);
    }
    
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    //I did not include the DELETE functionality intentionally due to instructions received, as well as 
    //to protect the integrity of the data.
    
}
